package Server.BusinessObjects;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Server.DataAccess.ConnectJDBCMySQL;

public class Scenario {

	private int id;
	private String name;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Scenario(ResultSet rs) throws SQLException {
		this.setId(rs.getInt("id"));
		this.setName(rs.getString("name"));
	}

	public static boolean existsInDb(String scenarioName) {
		ArrayList<Scenario> scenarios;
		try {
			scenarios = (ArrayList<Scenario>) Scenario.getScenarios();

			for (Scenario s : scenarios) {
				if (s.getName().equalsIgnoreCase(scenarioName)) {
					return true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	public static Scenario getScenario(int id) throws SQLException {
		String query = "Select * from scenarios where Id = ?";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, id);

		List<Scenario> list = Scenario.getScenarioList(ps);

		return list.size() == 1 ? list.get(0) : null;
	}

	public static List<Scenario> getScenarios() throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "SELECT * FROM scenarios";
		PreparedStatement ps = dbCon.prepareStatement(query);

		List<Scenario> list = Scenario.getScenarioList(ps);

		return list;
	}

	public static List<Category> getCategoriesToScenario(int scenarioId)
			throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "Select * from categories c, catScen cs Where c.id = cs.catId and cs.scenId=?";

		PreparedStatement ps = dbCon.prepareStatement(query);

		ps.setInt(1, scenarioId);

		List<Category> list = Scenario.getCategoryList(ps);

		return list;
	}

	public static List<Category> getCategoryList(PreparedStatement ps)
			throws SQLException {
		List<Category> output = new ArrayList<Category>();

		try {
			ResultSet rs = ps.executeQuery();

			while (rs.next())
				output.add(new Category(rs));

			rs.close();
		} finally {
			Connection con = ps.getConnection();
			if (ps != null)
				ps.close();

			if (con != null)
				con.close();

		}
		return output;
	}

	public static List<Scenario> getScenarioList(PreparedStatement ps)
			throws SQLException {
		List<Scenario> output = new ArrayList<Scenario>();

		try {
			ResultSet rs = ps.executeQuery();

			while (rs.next())
				output.add(new Scenario(rs));

			rs.close();
		} finally {
			Connection con = ps.getConnection();
			if (ps != null)
				ps.close();

			if (con != null)
				con.close();

		}
		return output;
	}

	public static int insertScenarioInDb(String scenarioName)
			throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "INSERT INTO scenarios (name) VALUES (?);";
		PreparedStatement ps = dbCon.prepareStatement(query);

		ps.setString(1, scenarioName);
		ps.execute();

		query = "SELECT LAST_INSERT_ID();";
		ps = dbCon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		rs.next();
		int newId = rs.getInt(1);

		// autocommit is on by default
		dbCon.close();

		return newId;
	}

	public static void deleteScenarioFromDb(int scenarioId) throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "DELETE FROM catscen WHERE scenId=?;";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setInt(1, scenarioId);
		ps.execute();

		query = "DELETE FROM highscores WHERE scenId=?;";
		ps = dbCon.prepareStatement(query);
		ps.setInt(1, scenarioId);
		ps.execute();

		query = "DELETE FROM scenarios WHERE id=?;";
		ps = dbCon.prepareStatement(query);
		ps.setInt(1, scenarioId);
		ps.execute();

		dbCon.close();

	}

	public static void updateScenarioInDb(int scenarioId, String newScenarioName)
			throws SQLException {
		Scenario s = Scenario.getScenario(scenarioId);

		if (newScenarioName.equalsIgnoreCase("null")) {
			newScenarioName = s.getName();
		}

		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "UPDATE scenarios SET name=? WHERE id=?";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setString(1, newScenarioName);
		ps.setInt(2, scenarioId);
		ps.execute();

		dbCon.close();

	}
}
